package com.batch_demo.excel;

import com.alibaba.fastjson.JSON;
import com.batch_demo.util.ExcelUtil;
import org.springframework.util.StringUtils;

import java.io.IOException;
import java.util.List;
import java.util.Map;

public class GeneratorSql {
    public void generateQ4Sql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/3-2017年Q4考核（普通员工有等级有系数）37人——20180106.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("【等级】");
            String m3 = map.get("【调整后系数】");
            System.out.println(String.format("update Evaluation set grade2 = '%s', coefficient = %s where id = %s;", m2, m3, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2017_41Sql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/2-2017年下半年考核（普通员工有等级有系数）41人——20180106.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");

            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("【调整后等级】");
            String m3 = map.get("【调整后系数】");
            System.out.println(String.format("update Evaluation set grade2 = '%s', coefficient = %s where id = %s;", m2, m3, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2017_12Sql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/1-2017年下半年考核(M2-3&P3-3及以上-半年度)-高职级SVPD调整12人——20180106.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("【调整后等级】");
            System.out.println(String.format("update Evaluation set grade2 = '%s' where id = %s;", m2, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2017_6Sql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/黄子威+杨晓燕+李可歌+孙自娟.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("【调整等级】");
            String m3 = map.get("【调整系数】");
            System.out.println(String.format("update Evaluation set grade2 = '%s', coefficient = %s where id = %s;", m2, m3, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2018_comments2Sql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/绩效系统特殊调整-20180109-to营轩.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("主管评语");
            System.out.println(String.format("update Evaluation set comments2 = '%s' where id = %s;", m2, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2018_grade2Sql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/绩效系统特殊调整-20180109-to营轩2.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("最终等级");
            String m3 = map.get("奖金系数");
            System.out.println(String.format("update Evaluation set grade2 = '%s', coefficient = %s where id = %s;", m2,m3, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2018_recoverSql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/绩效系统特殊调整-20180109-to营轩3.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("最终等级");
            System.out.println(String.format("update Evaluation set grade2 = '%s', status = 2, currentOperator = '000002' where id = %s;", m2, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate2018_cancelSql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/调整.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("入职时间");
            System.out.println(String.format("update Evaluation set status = 4, cancelReason = '%s' where id = %s;", m2, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台取消考核'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    // 亮哥直接D成绩回传-to张营轩-20180115.xlsx
    public void generate20180115_cancelSql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/亮哥直接D成绩回传-to张营轩-20180115.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("最终等级");
            String m3 = map.get("奖金系数");
            if (StringUtils.isEmpty(m3)) {
                m3 = null;
            }
            System.out.println(String.format("update Evaluation set grade2 = '%s', coefficient = %s where id = %s;", m2, m3, m1));
            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    // 亮哥直接D成绩回传-to张营轩-20180123.xlsx
    public void generate20180123_cancelSql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/to张营轩-20170123.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("最终等级");
            String m3 = map.get("主管评语");
            if (!StringUtils.isEmpty(m3)) {
                System.out.println(String.format("update Evaluation set grade2 = '%s', status = 2, comments2 = '%s' where id = %s;", m2, m3, m1));
            } else {
                System.out.println(String.format("update Evaluation set grade2 = '%s', status = 2 where id = %s;", m2, m1));
            }

            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate20180123_coefficientSql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/to张营轩-20170123-002.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("序号");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("最终等级");
            System.out.println(String.format("update Evaluation set grade2 = '%s' where id = %s;", m2, m1));

            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", m1, "'evaluation'","'后台调整绩效等级系数'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public void generate20180125_maoyanSql() throws IOException {
        List<Map<String, String>> mapList = ExcelUtil.read("/Users/zhangyingxuan/Downloads/20180107/20180125-maoyan.xlsx");
        for (Map<String, String> map : mapList) {
            String m1 = map.get("id");
            if (StringUtils.isEmpty(m1)) {
                continue;
            }
            String m2 = map.get("completeManagerChain");
            m2 = m2.substring(1);
            m2 = m2.substring(m2.indexOf(";"));
            m2 = m2.replace(";050493;", ";023976;");

            String m3 = map.get("completeManagerChain");
            m3 = m3.replace(";050493;", ";023976;");

            System.out.println(String.format("update Evaluation set managerChain = '%s', completeManagerChain = '%s' where id = %s;", m2, m3, new Double(Double.parseDouble(m1)).intValue()));

            System.out.println(String.format("insert into OprLog(referenceId, referenceType, action, info, tenantId) values (%s, %s, %s, %s, %s);", new Double(Double.parseDouble(m1)).intValue(), "'evaluation'","'后台调整猫眼汇报链'", "'"+JSON.toJSON(map)+"'", "'1'"));
        }
    }

    public static void main(String[] args) throws IOException {
        GeneratorSql generatorSql = new GeneratorSql();
//        generatorSql.generateQ4Sql();
//        generatorSql.generate2017_41Sql();
//        generatorSql.generate2017_12Sql();
//        generatorSql.generate2017_6Sql();
//        generatorSql.generate2018_comments2Sql();
//        generatorSql.generate2018_grade2Sql();
//        generatorSql.generate2018_recoverSql();
//        generatorSql.generate2018_cancelSql();
//        generatorSql.generate20180115_cancelSql();
//          generatorSql.generate20180123_cancelSql();
//        generatorSql.generate20180123_coefficientSql();
        generatorSql.generate20180125_maoyanSql();
    }
}
